package com.wanmait.spacesys.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.annotation.MultipartConfig;

import com.wanmait.spacesys.util.DbHelper;
import com.wanmait.spacesys.vo.Managerinfo;
import com.wanmait.spacesys.vo.Photo;
import com.wanmait.spacesys.vo.PostDetail;
import com.wanmait.spacesys.vo.ReportDetail;
import com.wanmait.spacesys.vo.ReportedMan;
import com.wanmait.spacesys.vo.UserDetail;
@MultipartConfig
public class ManagerinfoDAO {

	//拉黑用户查找
	public int findAllOfBlack(){
		int count = 0;
		//用户表查找
		String sql = "select count(*) as num from userinfo where enable=0 ";
		Connection conn = DbHelper.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			rs.next();
			count = rs.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			DbHelper.close(rs,ps,conn);
		}
		return count;
	};
	//所有用户查找
	public int findAllUserNumber() {
		return this.countNum("userinfo");
	}
	//查找帖子数量
	public int findAllPost() {
		return this.countNum("post");
	}
	
	//禁言用户数量
	public int findAllOfSpack() {
		return this.countNum("spack");
	}
	//封装查找：
	public int countNum(String table) {
		int count=0;
		String sql = "select count(*) as num from "+table;
		Connection conn = DbHelper.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		int countSpack=0;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			rs.next();
			count = rs.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			DbHelper.close(rs,ps,conn);
		}
		return count;
	}
	public void changePass(Integer id,String newPass) {
//		System.out.println("password"+newPass);
		String sql = "update managerinfo set password=? where id=?";
		Connection conn = DbHelper.getConnection();
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			DbHelper.update(sql, newPass,id);			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DbHelper.close(ps,conn);
		}
//		System.out.println(subjects);
		
	}
	public int uploadPhoto(Photo photo) {
		String sql = "insert into photo (pic,manage_id) values (?,?)";
		Connection conn = DbHelper.getConnection();
		PreparedStatement ps = null;
		int bingo =0 ;
		try {
			ps = conn.prepareStatement(sql);
			 bingo = DbHelper.update(sql,photo.getPic(),photo.getManagerId());			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DbHelper.close(ps,conn);
		}
		return bingo;
	}
	//获取所有用户信息 ：拉黑 禁言 举报 发帖子数 姓名 账号 昵称
	public List<UserDetail> userList(Integer pageNum, int countPage) {
		List<UserDetail> userList = new ArrayList();
		//sql  : 用户表  帖子表  禁言表 
		/*
		 * SELECT t.*, ifnull(spack.set_time , 0)AS '禁言时间' from
			(select COUNT(*)AS '发帖数' ,userinfo.*  from userinfo 
			left JOIN post ON userinfo.id = post.userinfo_id
			GROUP BY userinfo.id ) AS t
			LEFT JOIN spack ON t.id = spack.userinfo_id
		 */
		String sql = "SELECT t.*, ifnull(spack.set_time , 0)AS spack_time from\r\n"
				+ "(select userinfo.username,userinfo.nickname,userinfo.id,COUNT(*)AS '发帖数',userinfo.report,userinfo.enable  from userinfo \r\n"
				+ "left JOIN post ON userinfo.id = post.userinfo_id\r\n"
				+ "GROUP BY userinfo.id ) AS t\r\n"
				+ "LEFT JOIN spack ON t.id = spack.userinfo_id "
				+ " limit "+(pageNum*countPage)+","+countPage;
		Connection conn = DbHelper.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				UserDetail user = new UserDetail();
				user.setUsername(rs.getString(1));
				user.setNickname(rs.getString(2));
				user.setUserinfoId(rs.getInt(3));
				user.setPostCount(rs.getInt(4));
				user.setReport(rs.getInt(5));
				user.setEnable(rs.getBoolean(6));
				user.setSpackTime(rs.getInt(7));
				userList.add(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			DbHelper.close(rs,ps,conn);
		}
		return userList ;
	}
	//获取 黑名单用户
	public List<UserDetail> blackList(Integer pageNum, int countPage) {
		List<UserDetail> blackList = new ArrayList();
		//sql  : 用户表  帖子表  禁言表 
		String sql = "SELECT t.*, ifnull(spack.set_time , 0)AS spack_time from\r\n"
				+ "(select userinfo.username,userinfo.nickname,userinfo.id,COUNT(*)AS '发帖数',userinfo.report,userinfo.enable  from userinfo \r\n"
				+ "left JOIN post ON userinfo.id = post.userinfo_id\r\n"
				+ "GROUP BY userinfo.id ) AS t\r\n"
				+ "LEFT JOIN spack ON t.id = spack.userinfo_id "
				+ "WHERE t.enable=0 "
				+ " limit "+(pageNum*countPage)+","+countPage;
		Connection conn = DbHelper.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				UserDetail user = new UserDetail();
				user.setUsername(rs.getString(1));
				user.setNickname(rs.getString(2));
				user.setUserinfoId(rs.getInt(3));
				user.setPostCount(rs.getInt(4));
				user.setReport(rs.getInt(5));
				user.setEnable(rs.getBoolean(6));
				user.setSpackTime(rs.getInt(7));
				blackList.add(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			DbHelper.close(rs,ps,conn);
		}
		return blackList ;
	}
	public List<UserDetail> spackList(Integer pageNum, int countPage) {
		List<UserDetail> spackList = new ArrayList();
		//sql  : 用户表  帖子表  禁言表 
		String sql = "SELECT t.*, ifnull(spack.set_time , 0)AS spack_time from\r\n"
				+ "(select userinfo.username,userinfo.nickname,userinfo.id,COUNT(*)AS '发帖数',userinfo.report,userinfo.enable  from userinfo \r\n"
				+ "left JOIN post ON userinfo.id = post.userinfo_id\r\n"
				+ "GROUP BY userinfo.id ) AS t\r\n"
				+ "LEFT JOIN spack ON t.id = spack.userinfo_id "
				+ " where spack.set_time>0 "
				+ " limit "+(pageNum*countPage)+","+countPage;
		Connection conn = DbHelper.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				UserDetail user = new UserDetail();
				user.setUsername(rs.getString(1));
				user.setNickname(rs.getString(2));
				user.setUserinfoId(rs.getInt(3));
				user.setPostCount(rs.getInt(4));
				user.setReport(rs.getInt(5));
				user.setEnable(rs.getBoolean(6));
				user.setSpackTime(rs.getInt(7));
				spackList.add(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			DbHelper.close(rs,ps,conn);
		}
		return spackList ;
	}
	public List<PostDetail> noPass() {
		List<PostDetail> noPasses = new ArrayList();
		String sql = "SELECT post.id, post.title,post.content,post.pub_time ,userinfo.username FROM post\r\n"
				+ "LEFT JOIN userinfo ON userinfo.id = post.userinfo_id\r\n"
				+ "WHERE post.ispass = 0\r\n";
		Connection conn = DbHelper.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				PostDetail postDetail = new PostDetail();
				postDetail.setPostId(rs.getInt(1));
				postDetail.setTitle(rs.getString(2));
				postDetail.setContent(rs.getString(3));
				postDetail.setPubTime(rs.getString(4));
				postDetail.setUsername(rs.getString(5));
				noPasses.add(postDetail);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			DbHelper.close(rs,ps,conn);
		}
		return noPasses;
	}
	public int pageNumberNoPass() {
		String sql = "SELECT count(*) FROM post\r\n"
				+ "LEFT JOIN userinfo ON userinfo.id = post.userinfo_id\r\n"
				+ "WHERE post.ispass = 0\r\n";
		Connection conn = DbHelper.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			DbHelper.close(rs,ps,conn);
		}
		return 0;
	}
	public void clickNoPass(Integer postId) {
		String sql = "update post set ispass=? where id=?";
		Connection conn = DbHelper.getConnection();
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			DbHelper.update(sql,2,postId);			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DbHelper.close(ps,conn);
		}
	}
	//通过账号密码判断是否存在这个管理员
		public Managerinfo isExist(String account,String password) {
			Managerinfo managerinfo = null;
			String sql = "select * from managerinfo where account=? AND password=?";
			Connection conn = DbHelper.getConnection();
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				ps = conn.prepareStatement(sql);
				ps.setString(1, account);
				ps.setString(2, password);
				rs = ps.executeQuery();
				if(rs.next()) {
					managerinfo = new Managerinfo();
					managerinfo.setId(rs.getInt("id"));
					managerinfo.setAccount(account);
					managerinfo.setPassword(password);
					managerinfo.setNickname(rs.getString("nickname"));
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				DbHelper.close(rs, ps, conn);
			}
			return managerinfo;
			
		}
	public void clickPass(int postId) {
//		System.out.println("pass");
		String sql = "update post set ispass=? where id=?";
		Connection conn = DbHelper.getConnection();
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			DbHelper.update(sql,1,postId);			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DbHelper.close(ps,conn);
		}
	}
	public List<PostDetail> passList(int pageNum, int countPage) {
		List<PostDetail> passes = new ArrayList<>();
		String sql = "SELECT post.id, post.title,post.content,post.pub_time ,userinfo.username,post.ispass FROM post\r\n"
				+ "LEFT JOIN userinfo ON userinfo.id = post.userinfo_id\r\n"
				+ "WHERE post.ispass>0\r\n"
				+ "limit " +(pageNum*countPage)+ ","+countPage;
		Connection conn = DbHelper.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				PostDetail postDetail = new PostDetail();
				postDetail.setPostId(rs.getInt(1));
				postDetail.setTitle(rs.getString(2));
				postDetail.setContent(rs.getString(3));
				postDetail.setPubTime(rs.getString(4));
				postDetail.setUsername(rs.getString(5));
				postDetail.setIsPass(rs.getInt(6));
//				System.out.println(rs.getInt(6));
				passes.add(postDetail);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			DbHelper.close(rs,ps,conn);
		}
		return passes;
		
	}
	public int pageNumberPass() {
		String sql = "SELECT count(*) FROM post\r\n"
				+ "LEFT JOIN userinfo ON userinfo.id = post.userinfo_id\r\n"
				+ "WHERE post.ispass >0\r\n";
		Connection conn = DbHelper.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			DbHelper.close(rs,ps,conn);
		}
		return 0;
	}
	public List<ReportDetail> reportList() {
		List<ReportDetail> reports = new ArrayList<>();
		String sql = " SELECT userinfo.id AS reportedId, userinfo.username AS reportedName ,userinfo.report AS reportCount from userinfo\r\n"
				+ "WHERE report >= 3";
		Connection conn = DbHelper.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				ReportDetail report = new ReportDetail();
				report.setId(rs.getInt(1));
				report.setUsername(rs.getString(2));
				report.setCount(rs.getInt(3));
				reports.add(report);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			DbHelper.close(rs,ps,conn);
		}
		
		return reports;
	}
	public List<ReportedMan> reportDetail(Integer id) {
		List<ReportedMan> reports = new ArrayList<>();
		String sql = "SELECT a.* ,report.*,userinfo.username,DATE_FORMAT (report.report_time,'%Y-%m-%d %T')AS r_time FROM report \r\n"
				+ "inner JOIN \r\n"
				+ "(SELECT comment.id AS comment_id,comment.content FROM comment \r\n"
				+ "WHERE comment.userinfo_id ="+id +") AS a\r\n"
				+ "ON report.comment_id=a.comment_id\r\n"
				+ "LEFT JOIN userinfo \r\n"
				+ "ON userinfo.id = report.userinfo_id "
				+ "WHERE report.ischeck=0" ;
		Connection conn = DbHelper.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				ReportedMan reportedMan = new ReportedMan();
				reportedMan.setReportTime(rs.getString("r_time"));
				reportedMan.setReportReason(rs.getString("reason"));
				reportedMan.setReportMan(rs.getString("username"));
				reportedMan.setCommentId(rs.getInt("comment_id"));
				reportedMan.setComment(rs.getString("content"));
				reports.add(reportedMan);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			DbHelper.close(rs,ps,conn);
		}
		return reports;
	}
	public String findById(Integer id) {
		String sql = "SELECT userinfo.* FROM userinfo \r\n"
				+ "WHERE id = "+id;
		Connection conn = DbHelper.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String username = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				return rs.getString("username");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			DbHelper.close(rs,ps,conn);
		}
		return username;
	}
	public void clearCount(Integer id) {
//		System.out.println("pass");
		String sql = "update userinfo set report=? where id=?";
		Connection conn = DbHelper.getConnection();
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			DbHelper.update(sql,0,id);			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DbHelper.close(ps,conn);
		}
	}
	//举报表中 ischeck =1
	public void updateIsCheck(Integer id) {
		System.out.println("执行ischeck==0");
		String sql = "UPDATE report SET report.ischeck=1 WHERE report.id IN(\r\n"
				+ "(SELECT t.id from\r\n"
				+ "(SELECT a.* ,report.id,report.comment_id,report.userinfo_id,report.reason,report.ischeck\r\n"
				+ ",u.username,userinfo.username as rman,\r\n"
				+ "DATE_FORMAT (report.report_time,'%Y-%m-%d %T')AS r_time FROM report \r\n"
				+ "inner JOIN \r\n"
				+ "(SELECT comment.id AS commentid,comment.userinfo_id AS reportedman,comment.content FROM comment \r\n"
				+ "WHERE comment.userinfo_id =?) AS a\r\n"
				+ "ON report.comment_id=a.commentid\r\n"
				+ "LEFT JOIN userinfo AS u\r\n"
				+ "ON u.id = report.userinfo_id \r\n"
				+ "LEFT JOIN userinfo\r\n"
				+ "ON userinfo.id=a.reportedman)AS t) )\r\n";
				
		Connection conn = DbHelper.getConnection();
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			DbHelper.update(sql,id);			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			DbHelper.close(ps,conn);
		}
	}
	public void black(Integer id) {
			//enable=1 未被拉黑  ；=0被拉黑、
		String sql ="UPDATE userinfo SET ENABLE=? WHERE id =?";
				
		Connection conn = DbHelper.getConnection();
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			DbHelper.update(sql,0,id);			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			DbHelper.close(ps,conn);
		}
		//举报次数清零
		this.clearCount(id);
		//举报表中的 ischeck =1，已查看
		this.updateIsCheck(id);
	}
	public void sapck(Integer id, Integer managerId, Integer time) {
		//用户 id、管理者Id、禁言时间
		String sql ="INSERT INTO spack (set_time,userinfo_id,manager_id) VALUES (?,?,?)";
		Connection conn = DbHelper.getConnection();
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			DbHelper.update(sql,time,id,managerId);			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			DbHelper.close(ps,conn);
		}
		//举报次数清零
		this.clearCount(id);
		//举报表中的 ischeck =1，已查看
		this.updateIsCheck(id);
	}

	//查找举报人的 的评论分组
	
	public List<Integer> countComment(Integer id) {
		List<Integer> counts = new ArrayList<>();
		String sql = "SELECT a.commentid FROM report \r\n"
				+ "inner JOIN \r\n"
				+ "(SELECT comment.id AS commentid,comment.userinfo_id AS reportedman,comment.content FROM comment \r\n"
				+ "WHERE comment.userinfo_id =?) AS a\r\n"
				+ "ON report.comment_id=a.commentid\r\n"
				+ "LEFT JOIN userinfo AS u\r\n"
				+ "ON u.id = report.userinfo_id \r\n"
				+ "LEFT JOIN userinfo\r\n"
				+ "ON userinfo.id=a.reportedman\r\n"
				+ "GROUP BY a.commentid" ;
		Connection conn = DbHelper.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			while(rs.next()) {
				counts.add(rs.getInt(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			DbHelper.close(rs,ps,conn);
		}
		return counts;
	}
	public void deleteComment(int[] num, Integer id) {
		//举报表中的 ischeck =1，已查看
		this.updateIsCheck(id);
		for(int a:num) {
			String sql ="delete from comment where id=?";
			Connection conn = DbHelper.getConnection();
			PreparedStatement ps = null;
			try {
				ps = conn.prepareStatement(sql);
				DbHelper.update(sql,a);			
			} catch (SQLException e) {
				e.printStackTrace();
			}
			finally {
				DbHelper.close(ps,conn);
			}
			//举报次数清零
			this.clearCount(id);
			//System.out.println("举报次数清零");
//			System.out.println("ischeck=0");
		}
	}
	public int checkPrePass(String prePass,Integer id) {
		String sql = "SELECT managerinfo.password FROM managerinfo WHERE id =? AND PASSWORD=?";
		Connection conn = DbHelper.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			ps.setString(2, prePass);
			rs = ps.executeQuery();
			if(rs.next()) {
				return 1;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			DbHelper.close(rs,ps,conn);
		}
		return 0;
	}
	public int pageNumberReport() {
		String sql = "SELECT COUNT(userinfo.report)AS countRepman from userinfo\r\n"
				+ "WHERE report >= 3";
		Connection conn = DbHelper.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			if(rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			DbHelper.close(rs,ps,conn);
		}
		return 0;
	}
	
	
}
